package com.ss.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.ss.domain.Student;
import com.ss.listener.StudentListener;
import com.ss.mapper.StudentMapper;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.crypt.*;
import org.apache.poi.poifs.crypt.standard.StandardEncryptionVerifier;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bouncycastle.jce.provider.BouncyCastleProvider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.nio.charset.StandardCharsets;
import java.security.Security;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

@Service
public class StudentService {
    public static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    @Autowired
    StudentMapper studentMapper;
    @Resource
    StudentListener studentListener;

    public void importExcel(MultipartFile file) throws IOException {
        System.out.println("service...");
        EasyExcel.read(file.getInputStream(), Student.class, studentListener).doReadAll();
    }

    public void importExcelAsync(MultipartFile file) throws IOException {
        // 获取当前文件sheet数量（todo：待优化，是否可以通过文件名命名方式快速解析sheet数量）
        int numberOfSheets = WorkbookFactory.create(file.getInputStream()).getNumberOfSheets();
        // 创建具有和sheet数量相同数量线程得线程池
        ExecutorService executorService = Executors.newFixedThreadPool(numberOfSheets);
        // 让线程池中的线程并行解析不同的sheet，并写入数据库
        for(int i = 0; i < numberOfSheets; i++){
            int num = i;
            executorService.execute(
                    EasyExcel.read(file.getInputStream(), Student.class, studentListener).sheet(num)::doRead
            );
        }
    }

    public void exportExcel(HttpServletResponse response) throws IOException {
        response.setContentType(CONTENT_TYPE);
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + "test.xlsx");

        List<Student> students = studentMapper.selectList();
        EasyExcel.write(response.getOutputStream(), Student.class).sheet().doWrite(students);
    }

    /**
     * 使用POI实现导出加密excel文件
     */
    public ResponseEntity<ByteArrayResource> exportEncryptExcel(String secretKey) throws Exception {
        List<Student> students = studentMapper.selectList();

        // 创建工作簿和工作表
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Students");

        // 填充表头
        Row headerRow = sheet.createRow(0);
        String[] columns = {"id", "name", "age"};
        Field[] fields = Student.class.getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            field.setAccessible(true);
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(field.getName());
        }

        // 填充数据
//        int rowNum = 1;
//        for (Student student : students) {
//            Row row = sheet.createRow(rowNum++);
//            row.createCell(0).setCellValue(student.getId());
//            row.createCell(1).setCellValue(student.getName());
//            row.createCell(2).setCellValue(student.getAge());
//        }

        int rowNum = 1;
        for (Student student : students) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 0; i < fields.length; i++) {
                Field field = fields[i];
                field.setAccessible(true);
                row.createCell(i).setCellValue(field.get(student).toString());
            }
        }

        // ===============加密==============
        // 将工作簿写入到一个未加密的 Excel 文件中
        String filePath = "unEncrypt.xlsx";  // 未加密 Excel 文件的文件路径
        FileOutputStream fileOut = new FileOutputStream(filePath);
        workbook.write(fileOut);  // 将工作簿内容写入文件

        workbook.close();  // 写入完成后关闭工作簿，释放资源

        // 创建一个新的 POIFSFileSystem 对象，用于保存加密的数据
        POIFSFileSystem fs = new POIFSFileSystem();

        // 创建加密信息，使用 Agile 模式（现代 Office 文件使用的加密模式）
        EncryptionInfo info = new EncryptionInfo(EncryptionMode.agile);
        Encryptor encryptor = info.getEncryptor();  // 创建一个加密器
        encryptor.confirmPassword(secretKey);  // 设置加密文件的密码

        // 使用 OPCPackage 打开未加密的 Excel 文件
        OPCPackage opc = null;
        OutputStream os = null;
        try{
            opc = OPCPackage.open(new File(filePath), PackageAccess.READ_WRITE);
            os = encryptor.getDataStream(fs);
            opc.save(os);  // 将未加密的 Excel 数据写入到加密流中
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            // 在 finally 块中关闭资源，确保即使发生异常也能关闭资源
            os.close();
            opc.close();
        }



        // 将加密后的文件系统内容写入 ByteArrayOutputStream
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        fs.writeFilesystem(baos);
        fs.close();

        // 设置响应头并返回加密后的 Excel 文件
        byte[] encryptedExcelContent = baos.toByteArray();
        ByteArrayResource resource = new ByteArrayResource(encryptedExcelContent);

        HttpHeaders headers = new HttpHeaders();
        headers.add(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=encrypt.xlsx");
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);

        return new ResponseEntity<>(resource, headers, HttpStatus.OK);
    }

    public List<Student> selectList(){
        return studentMapper.selectList();
    }
}
